{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Handling duplicate, missing, or invalid data\n",
    "\n",
    "## About the data\n",
    "In this notebook, we will using daily weather data that was taken from the [National Centers for Environmental Information (NCEI) API](https://www.ncdc.noaa.gov/cdo-web/webservices/v2) and altered to introduce many common problems faced when working with data. \n",
    "\n",
    "*Note: The NCEI is part of the National Oceanic and Atmospheric Administration (NOAA) and, as you can see from the URL for the API, this resource was created when the NCEI was called the NCDC. Should the URL for this resource change in the future, you can search for the NCEI weather API to find the updated one.*\n",
    "\n",
    "## Background on the data\n",
    "\n",
    "Data meanings:\n",
    "- `PRCP`: precipitation in millimeters\n",
    "- `SNOW`: snowfall in millimeters\n",
    "- `SNWD`: snow depth in millimeters\n",
    "- `TMAX`: maximum daily temperature in Celsius\n",
    "- `TMIN`: minimum daily temperature in Celsius\n",
    "- `TOBS`: temperature at time of observation in Celsius\n",
    "- `WESF`: water equivalent of snow in millimeters\n",
    "\n",
    "Some important facts to get our bearings:\n",
    "- According to the National Weather Service, the coldest temperature ever recorded in Central Park was -15°F (-26.1°C) on February 9, 1934: [source](https://www.weather.gov/media/okx/Climate/CentralPark/extremes.pdf) \n",
    "- The temperature of the Sun's photosphere is approximately 5,505°C: [source](https://en.wikipedia.org/wiki/Sun)\n",
    "\n",
    "## Setup\n",
    "We need to import `pandas` and read in the long-format data to get started:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv('data/dirty_data.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Finding problematic data\n",
    "A good first step is to look at some rows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>station</th>\n",
       "      <th>PRCP</th>\n",
       "      <th>SNOW</th>\n",
       "      <th>SNWD</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "      <th>WESF</th>\n",
       "      <th>inclement_weather</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-01-01T00:00:00</td>\n",
       "      <td>?</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-01-01T00:00:00</td>\n",
       "      <td>?</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-01-01T00:00:00</td>\n",
       "      <td>?</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-01-02T00:00:00</td>\n",
       "      <td>GHCND:USC00280907</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-8.3</td>\n",
       "      <td>-16.1</td>\n",
       "      <td>-12.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-01-03T00:00:00</td>\n",
       "      <td>GHCND:USC00280907</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  date            station  PRCP  SNOW  SNWD    TMAX  TMIN  \\\n",
       "0  2018-01-01T00:00:00                  ?   0.0   0.0  -inf  5505.0 -40.0   \n",
       "1  2018-01-01T00:00:00                  ?   0.0   0.0  -inf  5505.0 -40.0   \n",
       "2  2018-01-01T00:00:00                  ?   0.0   0.0  -inf  5505.0 -40.0   \n",
       "3  2018-01-02T00:00:00  GHCND:USC00280907   0.0   0.0  -inf    -8.3 -16.1   \n",
       "4  2018-01-03T00:00:00  GHCND:USC00280907   0.0   0.0  -inf    -4.4 -13.9   \n",
       "\n",
       "   TOBS  WESF inclement_weather  \n",
       "0   NaN   NaN               NaN  \n",
       "1   NaN   NaN               NaN  \n",
       "2   NaN   NaN               NaN  \n",
       "3 -12.2   NaN             False  \n",
       "4 -13.3   NaN             False  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Looking at summary statistics can reveal strange or missing values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\users\\molinstefanie\\packt\\venv\\lib\\site-packages\\numpy\\lib\\function_base.py:3942: RuntimeWarning: invalid value encountered in multiply\n",
      "  x2 = take(ap, indices_above, axis=axis) * weights_above\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PRCP</th>\n",
       "      <th>SNOW</th>\n",
       "      <th>SNWD</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "      <th>WESF</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>765.000000</td>\n",
       "      <td>577.000000</td>\n",
       "      <td>577.000000</td>\n",
       "      <td>765.000000</td>\n",
       "      <td>765.000000</td>\n",
       "      <td>398.000000</td>\n",
       "      <td>11.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>5.360392</td>\n",
       "      <td>4.202773</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2649.175294</td>\n",
       "      <td>-15.914379</td>\n",
       "      <td>8.632161</td>\n",
       "      <td>16.290909</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>10.002138</td>\n",
       "      <td>25.086077</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2744.156281</td>\n",
       "      <td>24.242849</td>\n",
       "      <td>9.815054</td>\n",
       "      <td>9.489832</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-11.700000</td>\n",
       "      <td>-40.000000</td>\n",
       "      <td>-16.100000</td>\n",
       "      <td>1.800000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13.300000</td>\n",
       "      <td>-40.000000</td>\n",
       "      <td>0.150000</td>\n",
       "      <td>8.600000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>32.800000</td>\n",
       "      <td>-11.100000</td>\n",
       "      <td>8.300000</td>\n",
       "      <td>19.300000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>5.800000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5505.000000</td>\n",
       "      <td>6.700000</td>\n",
       "      <td>18.300000</td>\n",
       "      <td>24.900000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>61.700000</td>\n",
       "      <td>229.000000</td>\n",
       "      <td>inf</td>\n",
       "      <td>5505.000000</td>\n",
       "      <td>23.900000</td>\n",
       "      <td>26.100000</td>\n",
       "      <td>28.700000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             PRCP        SNOW        SNWD         TMAX        TMIN  \\\n",
       "count  765.000000  577.000000  577.000000   765.000000  765.000000   \n",
       "mean     5.360392    4.202773         NaN  2649.175294  -15.914379   \n",
       "std     10.002138   25.086077         NaN  2744.156281   24.242849   \n",
       "min      0.000000    0.000000        -inf   -11.700000  -40.000000   \n",
       "25%      0.000000    0.000000         NaN    13.300000  -40.000000   \n",
       "50%      0.000000    0.000000         NaN    32.800000  -11.100000   \n",
       "75%      5.800000    0.000000         NaN  5505.000000    6.700000   \n",
       "max     61.700000  229.000000         inf  5505.000000   23.900000   \n",
       "\n",
       "             TOBS       WESF  \n",
       "count  398.000000  11.000000  \n",
       "mean     8.632161  16.290909  \n",
       "std      9.815054   9.489832  \n",
       "min    -16.100000   1.800000  \n",
       "25%      0.150000   8.600000  \n",
       "50%      8.300000  19.300000  \n",
       "75%     18.300000  24.900000  \n",
       "max     26.100000  28.700000  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `info()` method can pinpoint missing values and wrong data types:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 765 entries, 0 to 764\n",
      "Data columns (total 10 columns):\n",
      "date                 765 non-null object\n",
      "station              765 non-null object\n",
      "PRCP                 765 non-null float64\n",
      "SNOW                 577 non-null float64\n",
      "SNWD                 577 non-null float64\n",
      "TMAX                 765 non-null float64\n",
      "TMIN                 765 non-null float64\n",
      "TOBS                 398 non-null float64\n",
      "WESF                 11 non-null float64\n",
      "inclement_weather    408 non-null object\n",
      "dtypes: float64(7), object(3)\n",
      "memory usage: 50.8+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use `pd.isnull()`/`pd.isna()` or the `isna()`/`isnull()` method of the series to find nulls:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "765"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "contain_nulls = df[\n",
    "    df.SNOW.isnull() | df.SNWD.isna()\\\n",
    "    | pd.isnull(df.TOBS) | pd.isna(df.WESF)\\\n",
    "    | df.inclement_weather.isna()\n",
    "]\n",
    "contain_nulls.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>station</th>\n",
       "      <th>PRCP</th>\n",
       "      <th>SNOW</th>\n",
       "      <th>SNWD</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "      <th>WESF</th>\n",
       "      <th>inclement_weather</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-01-01T00:00:00</td>\n",
       "      <td>?</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-01-01T00:00:00</td>\n",
       "      <td>?</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-01-01T00:00:00</td>\n",
       "      <td>?</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-01-02T00:00:00</td>\n",
       "      <td>GHCND:USC00280907</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-8.3</td>\n",
       "      <td>-16.1</td>\n",
       "      <td>-12.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-01-03T00:00:00</td>\n",
       "      <td>GHCND:USC00280907</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2018-01-03T00:00:00</td>\n",
       "      <td>GHCND:USC00280907</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2018-01-03T00:00:00</td>\n",
       "      <td>GHCND:USC00280907</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2018-01-04T00:00:00</td>\n",
       "      <td>?</td>\n",
       "      <td>20.6</td>\n",
       "      <td>229.0</td>\n",
       "      <td>inf</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19.3</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2018-01-04T00:00:00</td>\n",
       "      <td>?</td>\n",
       "      <td>20.6</td>\n",
       "      <td>229.0</td>\n",
       "      <td>inf</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19.3</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2018-01-05T00:00:00</td>\n",
       "      <td>?</td>\n",
       "      <td>0.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  date            station  PRCP   SNOW  SNWD    TMAX  TMIN  \\\n",
       "0  2018-01-01T00:00:00                  ?   0.0    0.0  -inf  5505.0 -40.0   \n",
       "1  2018-01-01T00:00:00                  ?   0.0    0.0  -inf  5505.0 -40.0   \n",
       "2  2018-01-01T00:00:00                  ?   0.0    0.0  -inf  5505.0 -40.0   \n",
       "3  2018-01-02T00:00:00  GHCND:USC00280907   0.0    0.0  -inf    -8.3 -16.1   \n",
       "4  2018-01-03T00:00:00  GHCND:USC00280907   0.0    0.0  -inf    -4.4 -13.9   \n",
       "5  2018-01-03T00:00:00  GHCND:USC00280907   0.0    0.0  -inf    -4.4 -13.9   \n",
       "6  2018-01-03T00:00:00  GHCND:USC00280907   0.0    0.0  -inf    -4.4 -13.9   \n",
       "7  2018-01-04T00:00:00                  ?  20.6  229.0   inf  5505.0 -40.0   \n",
       "8  2018-01-04T00:00:00                  ?  20.6  229.0   inf  5505.0 -40.0   \n",
       "9  2018-01-05T00:00:00                  ?   0.3    NaN   NaN  5505.0 -40.0   \n",
       "\n",
       "   TOBS  WESF inclement_weather  \n",
       "0   NaN   NaN               NaN  \n",
       "1   NaN   NaN               NaN  \n",
       "2   NaN   NaN               NaN  \n",
       "3 -12.2   NaN             False  \n",
       "4 -13.3   NaN             False  \n",
       "5 -13.3   NaN             False  \n",
       "6 -13.3   NaN             False  \n",
       "7   NaN  19.3              True  \n",
       "8   NaN  19.3              True  \n",
       "9   NaN   NaN               NaN  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "contain_nulls.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that we can't check if we have `NaN` like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.inclement_weather == 'NaN'].shape[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is because it is actually `np.nan`. However, notice this also doesn't work:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "df[df.inclement_weather == np.nan].shape[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have to use one of the methods discussed earlier for this to work:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "357"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.inclement_weather.isna()].shape[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can find `-inf`/`inf` by comparing to `-np.inf`/`np.inf`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "577"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.SNWD.isin([-np.inf, np.inf])].shape[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Rather than do this for each column, we can write a function that will use a [dictionary comprehension](https://www.python.org/dev/peps/pep-0274/) to check all the columns for us:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'date': 0,\n",
       " 'station': 0,\n",
       " 'PRCP': 0,\n",
       " 'SNOW': 0,\n",
       " 'SNWD': 577,\n",
       " 'TMAX': 0,\n",
       " 'TMIN': 0,\n",
       " 'TOBS': 0,\n",
       " 'WESF': 0,\n",
       " 'inclement_weather': 0}"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "\n",
    "def get_inf_count(df):\n",
    "    \"\"\"Find the number of inf/-inf values per column in the dataframe\"\"\"\n",
    "    return {\n",
    "        col : df[df[col].isin([np.inf, -np.inf])].shape[0] for col in df.columns\n",
    "    }\n",
    "\n",
    "get_inf_count(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Before we can decide how to handle the infinite values of snow depth, we should look at the summary statistics for snowfall which form a big part in determining the snow depth:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>np.inf Snow Depth</th>\n",
       "      <td>24.0</td>\n",
       "      <td>101.041667</td>\n",
       "      <td>74.498018</td>\n",
       "      <td>13.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>120.5</td>\n",
       "      <td>152.0</td>\n",
       "      <td>229.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>-np.inf Snow Depth</th>\n",
       "      <td>553.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    count        mean        std   min   25%    50%    75%  \\\n",
       "np.inf Snow Depth    24.0  101.041667  74.498018  13.0  25.0  120.5  152.0   \n",
       "-np.inf Snow Depth  553.0    0.000000   0.000000   0.0   0.0    0.0    0.0   \n",
       "\n",
       "                      max  \n",
       "np.inf Snow Depth   229.0  \n",
       "-np.inf Snow Depth    0.0  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame({\n",
    "    'np.inf Snow Depth': df[df.SNWD == np.inf].SNOW.describe(),\n",
    "    '-np.inf Snow Depth': df[df.SNWD == -np.inf].SNOW.describe()\n",
    "}).T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's now look into the `date` and `station` columns. We saw the `?` for station earlier, so we know that was the other unique value. However, we see that some dates are present 8 times in the data and we only have 324 days meaning we are also missing days:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>station</th>\n",
       "      <th>inclement_weather</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>765</td>\n",
       "      <td>765</td>\n",
       "      <td>408</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>324</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>2018-07-05T00:00:00</td>\n",
       "      <td>GHCND:USC00280907</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>8</td>\n",
       "      <td>398</td>\n",
       "      <td>384</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       date            station inclement_weather\n",
       "count                   765                765               408\n",
       "unique                  324                  2                 2\n",
       "top     2018-07-05T00:00:00  GHCND:USC00280907             False\n",
       "freq                      8                398               384"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe(include='object')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use the `duplicated()` method to find duplicate rows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "284"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.duplicated()].shape[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The default for `keep` is `'first'` meaning it won't show the first row that the duplicated data was seen in; we can pass in `False` to see it though:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "482"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.duplicated(keep=False)].shape[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also specify the columns to use:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "284"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.duplicated(['date', 'station'])].shape[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's look at a few duplicates. Just in the few values we see here, we know that the top 4 are actually in the data 6 times because by default we aren't seeing their first occurrence:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>station</th>\n",
       "      <th>PRCP</th>\n",
       "      <th>SNOW</th>\n",
       "      <th>SNWD</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "      <th>WESF</th>\n",
       "      <th>inclement_weather</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-01-01T00:00:00</td>\n",
       "      <td>?</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-01-01T00:00:00</td>\n",
       "      <td>?</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2018-01-03T00:00:00</td>\n",
       "      <td>GHCND:USC00280907</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2018-01-03T00:00:00</td>\n",
       "      <td>GHCND:USC00280907</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2018-01-04T00:00:00</td>\n",
       "      <td>?</td>\n",
       "      <td>20.6</td>\n",
       "      <td>229.0</td>\n",
       "      <td>inf</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19.3</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  date            station  PRCP   SNOW  SNWD    TMAX  TMIN  \\\n",
       "1  2018-01-01T00:00:00                  ?   0.0    0.0  -inf  5505.0 -40.0   \n",
       "2  2018-01-01T00:00:00                  ?   0.0    0.0  -inf  5505.0 -40.0   \n",
       "5  2018-01-03T00:00:00  GHCND:USC00280907   0.0    0.0  -inf    -4.4 -13.9   \n",
       "6  2018-01-03T00:00:00  GHCND:USC00280907   0.0    0.0  -inf    -4.4 -13.9   \n",
       "8  2018-01-04T00:00:00                  ?  20.6  229.0   inf  5505.0 -40.0   \n",
       "\n",
       "   TOBS  WESF inclement_weather  \n",
       "1   NaN   NaN               NaN  \n",
       "2   NaN   NaN               NaN  \n",
       "5 -13.3   NaN             False  \n",
       "6 -13.3   NaN             False  \n",
       "8   NaN  19.3              True  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.duplicated()].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Mitigating Issues\n",
    "\n",
    "### Handling duplicated data\n",
    "Since we know we have NY weather data and noticed we only had two entries for `station`, we may decide to drop the `station` column because we are only interested in the weather data. However, when dealing with duplicate data, we need to think of the ramifications of removing it. Notice we only have data for the `WESF` column when the station is `?`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['?'], dtype=object)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.WESF.notna()].station.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we determine it won't impact our analysis, we can use `drop_duplicates()` to remove them:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(324, 9)"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# save this information for later\n",
    "station_qm_wesf = df[df.station == '?'].WESF\n",
    "\n",
    "# sort ? to the bottom\n",
    "df.sort_values('station', ascending=False, inplace=True)\n",
    "\n",
    "# drop duplicates based on the date column keeping the first occurrence \n",
    "# which will be the valid station if it has data\n",
    "df_deduped = df.drop_duplicates('date').drop(\n",
    "    # remove the station column because we are done with it \n",
    "    # and WESF because we need to replace it later\n",
    "    columns=['station', 'WESF'] \n",
    ").sort_values('date').assign( # sort by the date\n",
    "    # add back the WESF column which will be properly matched because of the index\n",
    "    WESF=station_qm_wesf\n",
    ")\n",
    "\n",
    "df_deduped.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Check out the 4th row, we have `WESF` in the correct spot thanks to the index:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>PRCP</th>\n",
       "      <th>SNOW</th>\n",
       "      <th>SNWD</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "      <th>inclement_weather</th>\n",
       "      <th>WESF</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-01-01T00:00:00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-01-02T00:00:00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-8.3</td>\n",
       "      <td>-16.1</td>\n",
       "      <td>-12.2</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2018-01-03T00:00:00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.3</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2018-01-04T00:00:00</td>\n",
       "      <td>20.6</td>\n",
       "      <td>229.0</td>\n",
       "      <td>inf</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>19.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2018-01-05T00:00:00</td>\n",
       "      <td>14.2</td>\n",
       "      <td>127.0</td>\n",
       "      <td>inf</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   date  PRCP   SNOW  SNWD    TMAX  TMIN  TOBS  \\\n",
       "0   2018-01-01T00:00:00   0.0    0.0  -inf  5505.0 -40.0   NaN   \n",
       "3   2018-01-02T00:00:00   0.0    0.0  -inf    -8.3 -16.1 -12.2   \n",
       "6   2018-01-03T00:00:00   0.0    0.0  -inf    -4.4 -13.9 -13.3   \n",
       "8   2018-01-04T00:00:00  20.6  229.0   inf  5505.0 -40.0   NaN   \n",
       "11  2018-01-05T00:00:00  14.2  127.0   inf    -4.4 -13.9 -13.9   \n",
       "\n",
       "   inclement_weather  WESF  \n",
       "0                NaN   NaN  \n",
       "3              False   NaN  \n",
       "6              False   NaN  \n",
       "8               True  19.3  \n",
       "11              True   NaN  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_deduped.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dealing with nulls\n",
    "We could drop nulls, replace them with some arbitrary value, or impute them using the surrounding data. Each of these options may have ramifications, so we must choose wisely.\n",
    "\n",
    "We can use `dropna()` to drop rows where any column has a null value. The default options leave us without data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(0, 9)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_deduped.dropna().shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we pass `how='all'`, we can choose to only drop rows where everything is null, but this removes nothing:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(324, 9)"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_deduped.dropna(how='all').shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use just a subset of columns to determine what to drop with the `subset` argument:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(293, 9)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_deduped.dropna(\n",
    "    how='all', subset=['inclement_weather', 'SNOW', 'SNWD']\n",
    ").shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This can also be performed along columns, and we can also require a certain number of null values before we drop the data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['date', 'PRCP', 'SNOW', 'SNWD', 'TMAX', 'TMIN', 'TOBS',\n",
       "       'inclement_weather'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_deduped.dropna(axis='columns', thresh=df_deduped.shape[0]*.75).columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can choose to fill in the null values instead with `fillna()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>PRCP</th>\n",
       "      <th>SNOW</th>\n",
       "      <th>SNWD</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "      <th>inclement_weather</th>\n",
       "      <th>WESF</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-01-01T00:00:00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-01-02T00:00:00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-8.3</td>\n",
       "      <td>-16.1</td>\n",
       "      <td>-12.2</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2018-01-03T00:00:00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.3</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2018-01-04T00:00:00</td>\n",
       "      <td>20.6</td>\n",
       "      <td>229.0</td>\n",
       "      <td>inf</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>19.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2018-01-05T00:00:00</td>\n",
       "      <td>14.2</td>\n",
       "      <td>127.0</td>\n",
       "      <td>inf</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>True</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   date  PRCP   SNOW  SNWD    TMAX  TMIN  TOBS  \\\n",
       "0   2018-01-01T00:00:00   0.0    0.0  -inf  5505.0 -40.0   NaN   \n",
       "3   2018-01-02T00:00:00   0.0    0.0  -inf    -8.3 -16.1 -12.2   \n",
       "6   2018-01-03T00:00:00   0.0    0.0  -inf    -4.4 -13.9 -13.3   \n",
       "8   2018-01-04T00:00:00  20.6  229.0   inf  5505.0 -40.0   NaN   \n",
       "11  2018-01-05T00:00:00  14.2  127.0   inf    -4.4 -13.9 -13.9   \n",
       "\n",
       "   inclement_weather  WESF  \n",
       "0                NaN   0.0  \n",
       "3              False   0.0  \n",
       "6              False   0.0  \n",
       "8               True  19.3  \n",
       "11              True   0.0  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_deduped.loc[:,'WESF'].fillna(0, inplace=True)\n",
    "df_deduped.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "At this point we have done every we can without distorting the data. We know that we are missing dates, but if we reindex, we don't know how to fill in the NaN data. With the weather data, we can't assume because it snowed one day that it will snow the next or that the temperature will be the same. For this reason, note that the next few examples are just for illustrative purposes only—just because we can do something doesn't mean we should.\n",
    "\n",
    "That being said, let's try to address some of remaining issues with the temperature data. We know that when `TMAX` is the temperature of the Sun, it must be because there was no measured value, so let's replace it with `NaN` and then we will make an assumption that the temperature won't change drastically day-to-day. Note that this is actually a big assumption, but it will allow us to understand how `fillna()` works when we provide a strategy through the `method` parameter. We will also do this for `TMIN` which currently uses -40°C for its placeholder when we know that the coldest temperature ever recorded in NYC was -15°F (-26.1°C) on February 9, 1934.\n",
    "\n",
    "The `fillna()` method gives us 2 options for the `method` parameter:\n",
    "- 'ffill' to forward fill\n",
    "- 'bfill' to back fill\n",
    "\n",
    "*Note that `'nearest'` is missing because we are not reindexing.*\n",
    "\n",
    "Here, we will use `'ffill'` to show how this works:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>PRCP</th>\n",
       "      <th>SNOW</th>\n",
       "      <th>SNWD</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "      <th>inclement_weather</th>\n",
       "      <th>WESF</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-01-01T00:00:00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-01-02T00:00:00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-8.3</td>\n",
       "      <td>-16.1</td>\n",
       "      <td>-12.2</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2018-01-03T00:00:00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.3</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2018-01-04T00:00:00</td>\n",
       "      <td>20.6</td>\n",
       "      <td>229.0</td>\n",
       "      <td>inf</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>19.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2018-01-05T00:00:00</td>\n",
       "      <td>14.2</td>\n",
       "      <td>127.0</td>\n",
       "      <td>inf</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>True</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   date  PRCP   SNOW  SNWD  TMAX  TMIN  TOBS  \\\n",
       "0   2018-01-01T00:00:00   0.0    0.0  -inf   NaN   NaN   NaN   \n",
       "3   2018-01-02T00:00:00   0.0    0.0  -inf  -8.3 -16.1 -12.2   \n",
       "6   2018-01-03T00:00:00   0.0    0.0  -inf  -4.4 -13.9 -13.3   \n",
       "8   2018-01-04T00:00:00  20.6  229.0   inf  -4.4 -13.9   NaN   \n",
       "11  2018-01-05T00:00:00  14.2  127.0   inf  -4.4 -13.9 -13.9   \n",
       "\n",
       "   inclement_weather  WESF  \n",
       "0                NaN   0.0  \n",
       "3              False   0.0  \n",
       "6              False   0.0  \n",
       "8               True  19.3  \n",
       "11              True   0.0  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_deduped.assign(\n",
    "    TMAX=lambda x: x.TMAX.replace(5505, np.nan).fillna(method='ffill'),\n",
    "    TMIN=lambda x: x.TMIN.replace(-40, np.nan).fillna(method='ffill')\n",
    ").head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use `np.nan_to_num()` to turn `np.nan` into 0 and `-np.inf`/`np.inf` into large negative or positive finite numbers:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>PRCP</th>\n",
       "      <th>SNOW</th>\n",
       "      <th>SNWD</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "      <th>inclement_weather</th>\n",
       "      <th>WESF</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-01-01T00:00:00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-1.797693e+308</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-01-02T00:00:00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-1.797693e+308</td>\n",
       "      <td>-8.3</td>\n",
       "      <td>-16.1</td>\n",
       "      <td>-12.2</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2018-01-03T00:00:00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-1.797693e+308</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.3</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2018-01-04T00:00:00</td>\n",
       "      <td>20.6</td>\n",
       "      <td>229.0</td>\n",
       "      <td>1.797693e+308</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>19.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2018-01-05T00:00:00</td>\n",
       "      <td>14.2</td>\n",
       "      <td>127.0</td>\n",
       "      <td>1.797693e+308</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>True</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   date  PRCP   SNOW           SNWD    TMAX  TMIN  TOBS  \\\n",
       "0   2018-01-01T00:00:00   0.0    0.0 -1.797693e+308  5505.0 -40.0   NaN   \n",
       "3   2018-01-02T00:00:00   0.0    0.0 -1.797693e+308    -8.3 -16.1 -12.2   \n",
       "6   2018-01-03T00:00:00   0.0    0.0 -1.797693e+308    -4.4 -13.9 -13.3   \n",
       "8   2018-01-04T00:00:00  20.6  229.0  1.797693e+308  5505.0 -40.0   NaN   \n",
       "11  2018-01-05T00:00:00  14.2  127.0  1.797693e+308    -4.4 -13.9 -13.9   \n",
       "\n",
       "   inclement_weather  WESF  \n",
       "0                NaN   0.0  \n",
       "3              False   0.0  \n",
       "6              False   0.0  \n",
       "8               True  19.3  \n",
       "11              True   0.0  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_deduped.assign(\n",
    "    SNWD=lambda x: np.nan_to_num(x.SNWD)\n",
    ").head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can couple `fillna()` with other types of calculations for interpolation. Here we replace missing values of `TMAX` with the median of all `TMAX` values, `TMIN` with the median of all `TMIN` values, and `TOBS` to the average of the `TMAX` and `TMIN` values. Since we place `TOBS` last, we have access to the imputed values for `TMIN` and `TMAX` in the calculation. **WARNING: the text has a typo and fills in TMAX with TMIN's median, the below is correct.**:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>PRCP</th>\n",
       "      <th>SNOW</th>\n",
       "      <th>SNWD</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "      <th>inclement_weather</th>\n",
       "      <th>WESF</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-01-01T00:00:00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-01-02T00:00:00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-8.3</td>\n",
       "      <td>-16.1</td>\n",
       "      <td>-12.2</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2018-01-03T00:00:00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.3</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2018-01-04T00:00:00</td>\n",
       "      <td>20.6</td>\n",
       "      <td>229.0</td>\n",
       "      <td>inf</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>True</td>\n",
       "      <td>19.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2018-01-05T00:00:00</td>\n",
       "      <td>14.2</td>\n",
       "      <td>127.0</td>\n",
       "      <td>inf</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>True</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   date  PRCP   SNOW  SNWD  TMAX  TMIN  TOBS  \\\n",
       "0   2018-01-01T00:00:00   0.0    0.0  -inf  22.8   0.0  11.4   \n",
       "3   2018-01-02T00:00:00   0.0    0.0  -inf  -8.3 -16.1 -12.2   \n",
       "6   2018-01-03T00:00:00   0.0    0.0  -inf  -4.4 -13.9 -13.3   \n",
       "8   2018-01-04T00:00:00  20.6  229.0   inf  22.8   0.0  11.4   \n",
       "11  2018-01-05T00:00:00  14.2  127.0   inf  -4.4 -13.9 -13.9   \n",
       "\n",
       "   inclement_weather  WESF  \n",
       "0                NaN   0.0  \n",
       "3              False   0.0  \n",
       "6              False   0.0  \n",
       "8               True  19.3  \n",
       "11              True   0.0  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_deduped.assign(\n",
    "    TMAX=lambda x: x.TMAX.replace(5505, np.nan).fillna(x.TMAX.median()),\n",
    "    TMIN=lambda x: x.TMIN.replace(-40, np.nan).fillna(x.TMIN.median()),\n",
    "    # average of TMAX and TMIN\n",
    "    TOBS=lambda x: x.TOBS.fillna((x.TMAX + x.TMIN) / 2)\n",
    ").head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also use `apply()` for running the same calculation across columns. For example, let's fill all missing values with their rolling 7 day median of their values, setting the number of periods required for the calculation to 0 to ensure we don't introduce more extra `NaN` values. (Rolling calculations will be covered in [chapter 4](https://github.com/stefmolin/Hands-On-Data-Analysis-with-Pandas/tree/master/ch_04).) We need to set the `date` column as the index so `apply()` doesn't try to take the rolling 7 day median of the date:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PRCP</th>\n",
       "      <th>SNOW</th>\n",
       "      <th>SNWD</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "      <th>inclement_weather</th>\n",
       "      <th>WESF</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-01-01T00:00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-02T00:00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-8.30</td>\n",
       "      <td>-16.1</td>\n",
       "      <td>-12.20</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-03T00:00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-4.40</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.30</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-04T00:00:00</th>\n",
       "      <td>20.6</td>\n",
       "      <td>229.0</td>\n",
       "      <td>inf</td>\n",
       "      <td>-6.35</td>\n",
       "      <td>-15.0</td>\n",
       "      <td>-12.75</td>\n",
       "      <td>True</td>\n",
       "      <td>19.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-05T00:00:00</th>\n",
       "      <td>14.2</td>\n",
       "      <td>127.0</td>\n",
       "      <td>inf</td>\n",
       "      <td>-4.40</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.90</td>\n",
       "      <td>True</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-06T00:00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-10.00</td>\n",
       "      <td>-15.6</td>\n",
       "      <td>-15.00</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-07T00:00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-11.70</td>\n",
       "      <td>-17.2</td>\n",
       "      <td>-16.10</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-08T00:00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-7.80</td>\n",
       "      <td>-16.7</td>\n",
       "      <td>-8.30</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-10T00:00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>5.00</td>\n",
       "      <td>-7.8</td>\n",
       "      <td>-7.80</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-11T00:00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>4.40</td>\n",
       "      <td>-7.8</td>\n",
       "      <td>1.10</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     PRCP   SNOW  SNWD   TMAX  TMIN   TOBS inclement_weather  \\\n",
       "date                                                                           \n",
       "2018-01-01T00:00:00   0.0    0.0  -inf    NaN   NaN    NaN               NaN   \n",
       "2018-01-02T00:00:00   0.0    0.0  -inf  -8.30 -16.1 -12.20             False   \n",
       "2018-01-03T00:00:00   0.0    0.0  -inf  -4.40 -13.9 -13.30             False   \n",
       "2018-01-04T00:00:00  20.6  229.0   inf  -6.35 -15.0 -12.75              True   \n",
       "2018-01-05T00:00:00  14.2  127.0   inf  -4.40 -13.9 -13.90              True   \n",
       "2018-01-06T00:00:00   0.0    0.0  -inf -10.00 -15.6 -15.00             False   \n",
       "2018-01-07T00:00:00   0.0    0.0  -inf -11.70 -17.2 -16.10             False   \n",
       "2018-01-08T00:00:00   0.0    0.0  -inf  -7.80 -16.7  -8.30             False   \n",
       "2018-01-10T00:00:00   0.0    0.0  -inf   5.00  -7.8  -7.80             False   \n",
       "2018-01-11T00:00:00   0.0    0.0  -inf   4.40  -7.8   1.10             False   \n",
       "\n",
       "                     WESF  \n",
       "date                       \n",
       "2018-01-01T00:00:00   0.0  \n",
       "2018-01-02T00:00:00   0.0  \n",
       "2018-01-03T00:00:00   0.0  \n",
       "2018-01-04T00:00:00  19.3  \n",
       "2018-01-05T00:00:00   0.0  \n",
       "2018-01-06T00:00:00   0.0  \n",
       "2018-01-07T00:00:00   0.0  \n",
       "2018-01-08T00:00:00   0.0  \n",
       "2018-01-10T00:00:00   0.0  \n",
       "2018-01-11T00:00:00   0.0  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_deduped.assign(\n",
    "    # make TMAX and TMIN NaN where appropriate\n",
    "    TMAX=lambda x: x.TMAX.replace(5505, np.nan),\n",
    "    TMIN=lambda x: x.TMIN.replace(-40, np.nan)\n",
    ").set_index('date').apply(\n",
    "    # rolling calculations will be covered in chapter 4, this is a rolling 7 day median\n",
    "    # we set min_periods (# of periods required for calculation) to 0 so we always get a result \n",
    "    lambda x: x.fillna(x.rolling(7, min_periods=0).median())\n",
    ").head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The last strategy we could try is interpolation with the `interpolate()` method. We specify the `method` parameter with the interpolation strategy to use. There are many options, but we will stick with the default of `'linear'`, which will treat values as evenly spaced and place missing values in the middle of existing ones. We have some missing data, so we will reindex first. Look at January 9th, which we didn't have before—the values for `TMAX`, `TMIN`, and `TOBS` are the average of values the day prior (January 8th) and the day after (January 10th):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PRCP</th>\n",
       "      <th>SNOW</th>\n",
       "      <th>SNWD</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "      <th>inclement_weather</th>\n",
       "      <th>WESF</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-01-01</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-02</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-8.3</td>\n",
       "      <td>-16.10</td>\n",
       "      <td>-12.20</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-03</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.90</td>\n",
       "      <td>-13.30</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-04</th>\n",
       "      <td>20.6</td>\n",
       "      <td>229.0</td>\n",
       "      <td>inf</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.90</td>\n",
       "      <td>-13.60</td>\n",
       "      <td>True</td>\n",
       "      <td>19.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-05</th>\n",
       "      <td>14.2</td>\n",
       "      <td>127.0</td>\n",
       "      <td>inf</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.90</td>\n",
       "      <td>-13.90</td>\n",
       "      <td>True</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-06</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-10.0</td>\n",
       "      <td>-15.60</td>\n",
       "      <td>-15.00</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-07</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-11.7</td>\n",
       "      <td>-17.20</td>\n",
       "      <td>-16.10</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-08</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>-7.8</td>\n",
       "      <td>-16.70</td>\n",
       "      <td>-8.30</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-09</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.4</td>\n",
       "      <td>-12.25</td>\n",
       "      <td>-8.05</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-10</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-inf</td>\n",
       "      <td>5.0</td>\n",
       "      <td>-7.80</td>\n",
       "      <td>-7.80</td>\n",
       "      <td>False</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            PRCP   SNOW  SNWD  TMAX   TMIN   TOBS inclement_weather  WESF\n",
       "2018-01-01   0.0    0.0  -inf   NaN    NaN    NaN               NaN   0.0\n",
       "2018-01-02   0.0    0.0  -inf  -8.3 -16.10 -12.20             False   0.0\n",
       "2018-01-03   0.0    0.0  -inf  -4.4 -13.90 -13.30             False   0.0\n",
       "2018-01-04  20.6  229.0   inf  -4.4 -13.90 -13.60              True  19.3\n",
       "2018-01-05  14.2  127.0   inf  -4.4 -13.90 -13.90              True   0.0\n",
       "2018-01-06   0.0    0.0  -inf -10.0 -15.60 -15.00             False   0.0\n",
       "2018-01-07   0.0    0.0  -inf -11.7 -17.20 -16.10             False   0.0\n",
       "2018-01-08   0.0    0.0  -inf  -7.8 -16.70  -8.30             False   0.0\n",
       "2018-01-09   0.0    0.0   NaN  -1.4 -12.25  -8.05               NaN   0.0\n",
       "2018-01-10   0.0    0.0  -inf   5.0  -7.80  -7.80             False   0.0"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_deduped.assign(\n",
    "    # make TMAX and TMIN NaN where appropriate\n",
    "    TMAX=lambda x: x.TMAX.replace(5505, np.nan),\n",
    "    TMIN=lambda x: x.TMIN.replace(-40, np.nan),\n",
    "    date=lambda x: pd.to_datetime(x.date)\n",
    ").set_index('date').reindex(\n",
    "    pd.date_range('2018-01-01', '2018-12-31', freq='D')\n",
    ").apply(\n",
    "    lambda x: x.interpolate()\n",
    ").head(10)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
